Read XML data in SQL

Question

I want to query data from XML. I have managed to retrive data from another set of XML data but this are a bit problematic.

Bellow you see the data and the query that does not retrive any data.

    DECLARE @xml XML    
    SET @xml=N'<DocumentXML>
      <LoadApplicationResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Reaktor.Applikator.DTO">
        <Application>
          <EmbeddedProductList>
            <EmbeddedProduct>
              <Flag>false</Flag>
              <CustomData>
                <root xmlns="">
                  <Guaranteer ChangeTime="2012-04-28T08:50:07.5706054+02:00" ChangedBy="sven" OldValue="">
                    <Text>4</Text>
                  </Guaranteer>
                  <PercentGuarantee ChangeTime="2012-04-28T08:50:07.5706054+02:00" ChangedBy="sven" OldValue="">
                    <Number>100</Number>
                  </PercentGuarantee>
                </root>
              </CustomData>
              <DataChangeTime>2014-04-28T08:50:07.5706054+02:00</DataChangeTime>
              <ID>12</ID>
              <FinanceSeparately>false</FinanceSeparately>
              <Guid>5349efcd-457c-4423-b4bb-a28f97dd5e64</Guid>
              <PluginData i:nil="true" />
              <PriceCalcTime>2014-04-28T08:50:09.2580946+02:00</PriceCalcTime>
              <Data>
                <root xmlns="">
                  <root TableId="192">
                    <Generic.TypeCode>abba</Generic.TypeCode>
                  </root>
                </root>
              </Data>
            </EmbeddedProduct>
            <EmbeddedProduct>
              <Flag>false</Flag>
              <CustomData i:nil="true" />
              <DataChangeTime>1954-10-03T00:00:00</DataChangeTime>
              <ID>30</ID>
              <FinanceSeparately>false</FinanceSeparately>
              <Guid>d587b9b4-94df-4d9b-ba0d-2fdc62823a17</Guid>
              <PluginData i:nil="true" />
              <PriceCalcTime>2014-04-28T08:49:55.8831802+02:00</PriceCalcTime>
              <Data>
                <root xmlns="">
                  <root TableId="013">
                    <EmbProd.CMSPrice>0</EmbProd.CMSPrice>
                    <EmbProd.MonthFee Operator="DBLMUL" Target="CUSTOM.EPTermFee.ADD" Source="XPATH://PaySeries[1]/TermLength" DFValue="200">200</EmbProd.MonthFee>
                  </root>
                  <root TableId="759" GroupText="210" GroupText0="210">
                    <Flag>ink</Flag>
                    <Generic.TypeCode>fil</Generic.TypeCode>
                  </root>
                </root>
              </Data>
            </EmbeddedProduct>
            <EmbeddedProduct>
              <Flag>false</Flag>
              <CustomData>
                <root xmlns="" />
              </CustomData>
              <DataChangeTime>2012-04-26T14:41:26.4232222+02:00</DataChangeTime>
              <ID>16</ID>
              <FinanceSeparately>false</FinanceSeparately>
              <Guid>c2e2343f-a5d6-43c8-aa18-c43419d20165</Guid>
              <PluginData i:nil="true" />
              <PriceCalcTime>2014-04-28T08:49:55.8831802+02:00</PriceCalcTime>
              <Data>
                <root xmlns="">
                  <root TableId="102">
                    <EmbProd.MonthFee Operator="DBLMUL" Target="CUSTOM.EPTermFee.ADD" Source="XPATH://PaySeries[1]/TermLength" DFValue="300">300</EmbProd.MonthFee>
                    <EP.GenericCost Target="COST">114</EP.GenericCost>
                  </root>
                  <root TableId="102" GroupText="11" GroupText0="7">
                    <EP.TermCount Target="DBLMUL">13</EP.TermCount>
                  </root>
                  <root TableId="102" GroupText="210" GroupText0="210">
                    <Generic.TypeCode>frodinge</Generic.TypeCode>
                  </root>
                </root>
              </Data>
            </EmbeddedProduct>
          </EmbeddedProductList>
         </Application>
      </LoadApplicationResult>
    </DocumentXML>'

SELECT tab.col.value('(Flag)[1]', 'nvarchar(max)') AS Flag
    ,tab.col.value('(Data/root/EmbProd.MonthFee)[1]', 'nvarchar(max)') AS Value
    ,tab.col.value('(ID)[1]', 'nvarchar(max)') AS Product
FROM @xml.nodes('/DocumentXML//LoadApplicationResult/Application/EmbeddedProductList/EmbeddedProduct') AS Tab(col)

The expected output should look like this:

    +-------+-------+---------+
    | Flag  | Value | Product |
    +-------+-------+---------+
    | false |       |      12 |
    | false |   200 |      30 |
    | true  |   300 |      16 |
    +-------+-------+---------+

Serg · Accepted Answer · 2016-12-06 16:52:46Z

1

You need to specify namespace

WITH XMLNAMESPACES ( 'http://schemas.datacontract.org/2004/07/Reaktor.Applikator.DTO'  as x)
SELECT tab.col.value('(x:Flag)[1]', 'nvarchar(max)') AS Flag
    ,tab.col.value('(x:Data/root/root/EmbProd.MonthFee)[1]', 'nvarchar(max)') AS Value
    ,tab.col.value('(x:ID)[1]', 'nvarchar(max)') AS Product
FROM @xml.nodes('DocumentXML/x:LoadApplicationResult/x:Application/x:EmbeddedProductList/x:EmbeddedProduct') AS Tab(col);

answered Dec 6, 2016 at 16:52

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Read XML data in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related