Querying XML within SQL Server with namespace

Question

I'm trying to query some XML data that I was sent. I have followed various examples, but can't even address any of the elements beneath the root. I'm not sure what I'm missing. I've tried simply pulling the xml beneath /KitchenSupply/StoreInfo and nothing. Is the namespace the culprit for me not being able to return anything?

declare @myxmlinv as xml = 
'<?xml version="1.0" encoding="utf-8"?>
<KitchenSupply xmlns="http://www.starstandards.org/STAR" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="KitchenSupply.xsd">
  <StoreInfo>
    <RCPT>
      <SubTaskCode>ZZZ</SubTaskCode>
      <SubComponentCode>BS1</SubComponentCode>
      <StoreNumber>2241</StoreNumber>
      <USRegionNumber>SE</USRegionNumber>
    </RCPT>
    <SentDateTime>02/04/2015</SentDateTime>
    <IntId>ABC1234587</IntId>
    <Destinations>
      <DestinationFormatType>KS_XML_v3</DestinationFormatType>
    </Destinations>
  </StoreInfo>
  <KitchenOrder>
    <KsRecord SalesTransactionPayType="CC" SalesTransactionType="Closed">
      <ReceiptAmt RcptTotalAmt="0.00" CustRcptTotalAmt="25.22" CCPostDate="01/07/2015" InvoiceDate="01/05/2015" CustName="JOHN SMITH" CustNo="13998" RcptNo="78476221" />
      <KsCosts>
        <SaleAmts UnitCost="15.00" TxblAmt="15.00" PayType="Cust" />
        <SalesInfo JobStatus="F" JobNo="1" ItemCode="HT093" ItemDesc="Hand Towel">
          <EmpInfo EmpRate="16.00" EmpCommPct="1.2" EmpName="DOUG ROGERS" EmpNo="998331" />
        </SalesInfo>
      </KsCosts>
    </KsRecord>
    <CustomerRecord>
      <ContactInfo LastName="SMITH" FreqFlag="Y">
        <Address Zip="90210" State="CA" City="BEV" Addr1="123 MAIN ST" Type="Business" />
        <Email MailTo="[email protected]" />
        <Phone Num="1235551212" Type="H" />
        <Phone Num="1235551213" Type="B" />
      </ContactInfo>
    </CustomerRecord>
  </KitchenOrder>
  <KitchenOrder>
    <KsRecord SalesTransactionPayType="CC" SalesTransactionType="Closed">
      <ReceiptAmt RcptTotalAmt="0.00" CustRcptTotalAmt="5.71" CCPostDate="01/08/2015" InvoiceDate="01/07/2015" CustName="SARAH BALDWIN" CustNo="14421" RcptNo="78476242" />
      <KsCosts>
        <SaleAmts UnitCost="2.00" TxblAmt="2.00" PayType="Cust" />
        <SalesInfo JobStatus="F" JobNo="1" ItemCode="HS044" ItemDesc="Hand Soap">
          <EmpInfo EmpRate="16.00" EmpCommPct="1.2" EmpName="DOUG ROGERS" EmpNo="998331" />
        </SalesInfo>
      </KsCosts>
    </KsRecord>
    <CustomerRecord>
      <ContactInfo LastName="BALDWIN" FreqFlag="N">
        <Address Zip="90210" State="CA" City="BEV" Addr1="123 VINE ST" Type="Home" />
        <Email MailTo="[email protected]" />
        <Phone Num="1235555512" Type="H" />
        <Phone Num="1235556613" Type="M" />
      </ContactInfo>
    </CustomerRecord>
  </KitchenOrder>
</KitchenSupply>';


declare @myxmlinv_table as table (
 row_id tinyint,
 inv_xml xml   
);

insert into @myxmlinv_table(row_id,inv_xml) values('1',@myxmlinv);

Display the XML document and pull the IntId column: (doesn't work)

select i.row_id, i.inv_xml, i.inv_xml.value('(/KitchenSupply/StoreInfo/IntId)[1]','varchar(255)') as data_description
from @myxmlinv_table i

Attempt to use XMLNAMESPACES to display the XML document at the StoreInfo level: (also doesn't work)

WITH XMLNAMESPACES ('http://www.starstandards.org/STAR' as ks)
SELECT 
   XmlCol.query('/ks:KitchenSupply/StoreInfo')
FROM T;

Ideally, I'd like to use the nodes to extract all the data out and in to separate tables for querying.

KitchenOrders all within one table, CustomerRecord within another, etc.

Any ideas?

marc_s · Accepted Answer · 2015-02-04 21:22:10Z

1

You need to use something like this:

-- define the XML namespace as the "default" so you don't have to 
-- prefix each and every XPath element with the XML namespace prefix
WITH XMLNAMESPACES( DEFAULT 'http://www.starstandards.org/STAR')
SELECT
    -- reach into the <KsRecord> subnode under <KitchenOrder>
    ReceiptNo = XC.value('(KsRecord/ReceiptAmt/@RcptNo)[1]', 'int'),
    CustName = XC.value('(KsRecord/ReceiptAmt/@CustName)[1]', 'varchar(25)'),
    -- reach into the <CustomerRecord> subnode under <KitchenOrder>     
    CustomerLastName = xc.value('(CustomerRecord/ContactInfo/@LastName)[1]', 'varchar(50)'),
    CustomerEmail = xc.value('(CustomerRecord/ContactInfo/Email/@MailTo)[1]', 'varchar(50)')
FROM
    -- get a "virtual" table of XML fragments, one for each <KitchenOrder> node
    @myxmlinv.nodes('/KitchenSupply/KitchenOrder') AS XT(XC)

answered Feb 4, 2015 at 21:22

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2162331 Over a year ago

Looks and works great! Thank you, hopefully I can expand upon this.

jhilden · Accepted Answer · 2015-02-04 21:47:46Z

1

Yes, it's the namespace. To get SQL Server to ignore the namespace you can use local-name:

SELECT *
FROM @myxmlinv_table
WHERE inv_xml.exist('//*[local-name()="KitchenSupply"]') = 1

answered Feb 4, 2015 at 21:47

jhilden

12.5k5 gold badges56 silver badges77 bronze badges

Collectives™ on Stack Overflow

Querying XML within SQL Server with namespace

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related