1

I've imported several XML files whilst gathering data into a SQL Server table.

I need to query this XML data column to transform the single XML column into a more structured table.

Can someone please help with the syntax here, the XML structure has a pattern hence once understood in SQL XML.query format it should be easy to repeat for all elements I want.

Select TOP 1
   [XMLFILE].value('(/Objs/Obj[0]/TN)[0]','VARCHAR(MAX)') as TEST,
   [XMLFILE].query('/Objs/Obj') as HEADERTEST,
   [XMLFILE].query('/Ref[@id="0"]')  as Property1
FROM 
   SQL_Table

The above just returns an empty string for every pattern I try.

I need to extract the name and value of each obj [] element.

XML structure example i.e. objs header followed by obj nodes 0 through to 'x' nodes.

<?xml version="1.0"?>
<Objs xmlns="xxxx" Version="1.1.0.1">
  <Obj RefId="0">
    <TN RefId="0">
      <T>xxxx/T>
      <T>xxxxx</T>
      <T>xxxx</T>
    </TN>
    <MS>
      <S N="Name">xxxx</S>
      <S N="Value">xxxx</S>
    </MS>
  </Obj>
  <Obj RefId="1">
    <TNRef RefId="0"/>
    <MS>
      <S N="Name">xxxxxx</S>
      <S N="Value"/>
    </MS>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0"/>
    <MS>
      <S N="Name">xxxxx</S>
      <S N="Value"/>
    </MS>
  </Obj>
  <Obj RefId="3">
    <TNRef RefId="0"/>
    <MS>
      <S N="Name">xxxx</S>
      <S N="Value"/>
    </MS>
  </Obj>
......
......
</Objs>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3707945
  • 45
  • 1
  • 7
  • Have a look at the answer to this question: http://stackoverflow.com/a/19165348/1208914 it might possibly be a duplicate – stormCloud Jun 04 '14 at 16:57

1 Answers1

0

Couple of problems:

  1. you're not respecting the XML namespace defined on your XML top level node
  2. you're not getting the appropriate nodes from your XML

Try this:

-- replace the 'xxx' with whatever is really defined in your <Objs xmlns=....> 
;WITH XMLNAMESPACES(DEFAULT 'xxxx')  
SELECT
    RefId = xc.value('@RefId', 'int'),
    Name = xc.value('(MS/S[@N="Name"])[1]', 'varchar(20)'),
    Value = xc.value('(MS/S[@N="Value"])[1]', 'varchar(20)')
FROM 
   SQL_Table
CROSS APPLY
    XmlFile.nodes('/Objs/Obj') AS XT(XC)

The CROSS APPLY generates an on-the-fly virtual table of XML fragments for each XML node that matches the XPath expression - here, for each <Obj> node under <Objs>.

The XQuery method .value() then "reaches" into that XML fragment (for each of the fragements in that virtual table) and pulls out the necessary desired info - here the RefId attribute on the <Obj> node, as well as the textual value of the <MS>/<S N="Name"> and <MS>/<S N="Value"> subnodes.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @user3707945: if this answer helped you solve your problem, then please [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Jun 05 '14 at 09:39