CODEWITHSUNDEEP
sqlsql-serverxmlxpath
Tamara
Tamara

Reputation: 65

Select an element in nested XML in SQL Server

I have a column with XML contents in a SQL Server table.

Actually, several different XML's are merged together in this particular XML column.

When I do

select
    columnwithXML.query('(/root/secondlayer/node())').value('(/*)[11]', 'varchar(max)')

I actually get the one XML that I really need and it starts with this:

<?xml version="1.0" encoding="utf-16"?>
<thirdlayer xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <someVariable1>false</someVariable1>
    <someVariable2>false</someVariable2>

By the way: there are many siblings on that <thirdlayer> level. I'm only interested in this one. I'm now reaching that one with the [11].

I want to read the contents of someVariable2. I have tried so many different options, but most of them return NULL. I'm not quite sure how to build the XML path especially since.

If someone has a suggestion, that would be much appreciated.

Thank you in advance

Added: part of the XML. I took out parts of it to make it easier to read.

<Configurations><Config><Label>IYE</Label><Value>Ja</Value><Description>xxxx</Description></Config><Config><Label>xxxx</Label><Value><?xml version="1.0" encoding="utf-16"?> <ArrayOfxxxx xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <ProductOption> (...) </ProductOption> <ProductOption> (...) </ProductOption> </ArrayOfyyyyy></Value><Description/> </Config><Config><Label>TICKET</Label><Value><?xml version="1.0" encoding="utf-8"?> <Ticket xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Sections> (...) </Sections> <PriceSections> (...) </PriceSections> </Ticket></Value><Description/></Config><Config><Label>CONFIGURATION</Label><Value><?xml version="1.0" encoding="utf-16"?> <ConfigurationData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <VariableX>false</VariableX> <VariableY>false</VariableY> </ConfigurationData></Value><Description/></Config></Configurations>

I have been able to get to the part where I only have:

<ConfigurationData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <VariableX>false</VariableX> <VariableY>false</VariableY> </ConfigurationData>

And then I'm stuck.

Upvotes: 1

Views: 1063

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Within SQL Server your xml cannot be stored as XML (due to the internally placed text/xmldecl elements (the <?xml version="1.0" encoding="utf-8"?>). This is - generally spoken - not allowed.

I have no idea how you managed to reach

I have been able to get to the part where I only have ...

Did you do this with string methods?

Who is creating these XMLs? If this is under your control, you should do this without the declarations. Alternatively (and not recommended) you might enclose all embedded XML within CDATA and read it as string (bad performance as you'll have to cast it to XML).

A solution for the existing XML

You can do a hack on string level like I do it here:

DECLARE @YourXML NVARCHAR(MAX)=
N'<Configurations><Config><Label>IYE</Label><Value>Ja</Value><Description>xxxx</Description></Config><Config><Label>xxxx</Label><Value><?xml version="1.0" encoding="utf-16"?>
<ArrayOfxxxx xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductOption>
    (...)
  </ProductOption>
  <ProductOption>
    (...)
  </ProductOption>
</ArrayOfxxxx></Value><Description/>
</Config><Config><Label>TICKET</Label><Value><?xml version="1.0" encoding="utf-8"?>
<Ticket xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Sections>
        (...)
    </Sections>
    <PriceSections>
     (...)
    </PriceSections>
    </Ticket></Value><Description/></Config><Config><Label>CONFIGURATION</Label><Value><?xml version="1.0" encoding="utf-16"?>
<ConfigurationData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <VariableX>false</VariableX>
  <VariableY>false</VariableY>
</ConfigurationData></Value><Description/></Config></Configurations>';

--I use REPLACE twice to convert the declarations to comments.
--Then I use .query() to read the <Config> element where the <Label> is "CONFIGURATION".
--Below this I go down to ConfigurationData and return this from a CTE.

WITH GetMyNode(ConfigurationData) AS
(
    SELECT CAST(REPLACE(REPLACE(@YourXML,'<?','<!--?'),'?>','?-->') AS XML)
           .query('/Configurations/Config[(Label/text())[1]="CONFIGURATION"]/Value/ConfigurationData')
)
--read the CTEs XML and retrieve the two Variables
SELECT ConfigurationData.value('(ConfigurationData/VariableX/text())[1]','nvarchar(max)') AS VariableX
      ,ConfigurationData.value('(ConfigurationData/VariableY/text())[1]','nvarchar(max)') AS VariableY
FROM GetMyNode

Upvotes: 1

Related Questions