XML Query

• mviggers

  Old Hand

  Points: 339

  More actions

  July 6, 2018 at 9:05 am

  #407134

  Hi

  I have a row/column oriented xml to be inserted into a table. How do I write a XQuery (????? in code below) so all records (in example 3 records) will inserted into table @person?
  
  declare @xml xml
select @xml =
'<RESULTS>
<ROW>
  <COLUMN NAME="FIRSTNAME"><![CDATA[John]]></COLUMN>
  <COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
  <COLUMN NAME="COUNTRY"><![CDATA[DK]]></COLUMN>
</ROW>
<ROW>
  <COLUMN NAME="FIRSTNAME"><![CDATA[Jane]]></COLUMN>
  <COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
  <COLUMN NAME="COUNTRY"><![CDATA[SK]]></COLUMN>
</ROW>
<ROW>
  <COLUMN NAME="FIRSTNAME"><![CDATA[Joan]]></COLUMN>
  <COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
  <COLUMN NAME="COUNTRY"><![CDATA[UK]]></COLUMN>
</ROW>
</RESULTS>'

  declare @person table (firstname varchar(max), lastname varchar(max), country varchar(max))
  insert @person
  select ?????
  

  Any hint/advise is appreciated.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  July 6, 2018 at 9:20 am

  #1996565

  SELECT t.x.value('COLUMN[1]', 'varchar(50)') AS FirstName
    , t.x.value('COLUMN[2]', 'varchar(50)') AS LastName
    , t.x.value('COLUMN[3]', 'varchar(50)') AS CountryName
  FROM @xml.nodes('/RESULTS/ROW') AS t(x);

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  July 6, 2018 at 12:16 pm

  #1996602

  You really shouldn't use varchar(max) in this case.  Have you ever come across a first name, or a last name that is longer than 50 characters?
  You also want to normalise that structure.  Store Country in its own table, and store the CountryID or CountryISOCode in your person table.

  ---

  How to post data/code on a forum to get the best help.

• mviggers

  Old Hand

  Points: 339

  More actions

  July 6, 2018 at 12:30 pm

  #1996606

  DesNorton - Friday, July 6, 2018 12:16 PM

  You really shouldn't use varchar(max) in this case.  Have you ever come across a first name, or a last name that is longer than 50 characters?
  You also want to normalise that structure.  Store Country in its own table, and store the CountryID or CountryISOCode in your person table.

  The question was not about normalization nor datatypes. I am fully aware about best practices. The question was how to load the XML content (one-to-one) into a data table, which Steve Jones has answered to perfection. The XML is a scripted output of an Oracle DB that needs to be loaded into MSSQL (one-to-one). In the Oracle DB the fields are of datatype "varchar2 max length" (not my decision!). Thus, varchar(max) is needed in MSSQL to prevent data loss (IF content length - somehow - should be longer than 50 chars).

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  July 6, 2018 at 12:46 pm

  #1996611

  One last hint, the case matters. I made the mistake of typing "Row" the first time and got no results.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  July 7, 2018 at 12:53 am

  #1996643

  Be careful relying on the elements ordinal position if the ordinal position is not set in an XSD, i.e. using a typed XML column. It is much safer to use the name attribute of the column element. Further, if one or more of the elements are missing, the results will most likely be wrong.
  😎

  It will slightly affect performance but at least the output is accurate. 
  NOTE: always use the text() function to retrieve node values, it bypasses a reconstruct of XML in the output, can cut the server's effort in half.

  
USE TEEST;
GO
SET NOCOUNT ON;
-- https://www.sqlservercentral.com/Forums/1975183/XML-Query
DECLARE @TXML XML =
'<RESULTS>
<ROW>
<COLUMN NAME="FIRSTNAME"><![CDATA[John]]></COLUMN>
<COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
<COLUMN NAME="COUNTRY"><![CDATA[DK]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="FIRSTNAME"><![CDATA[Jane]]></COLUMN>
<COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
<COLUMN NAME="COUNTRY"><![CDATA[SK]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="FIRSTNAME"><![CDATA[Joan]]></COLUMN>
<COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
<COLUMN NAME="COUNTRY"><![CDATA[UK]]></COLUMN>
</ROW>
</RESULTS>';
;WITH ROW_DATA AS
(
  SELECT
   ROW_NUMBER() OVER (ORDER BY @@VERSION) AS RRID
   ,ROW.DATA.query('*') AS RXML
  FROM @TXML.nodes('RESULTS/ROW') ROW(DATA)
)
SELECT
  RD.RRID
 ,RD.RXML.value('(COLUMN[@NAME="FIRSTNAME"]/text())[1]','VARCHAR(50)') AS FIRSTNAME
 ,RD.RXML.value('(COLUMN[@NAME="LASTNAME"]/text())[1]','VARCHAR(50)') AS LASTNAME
 ,RD.RXML.value('(COLUMN[@NAME="COUNTRY"]/text())[1]','VARCHAR(50)') AS COUNTRY
FROM ROW_DATA RD;


• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  July 10, 2018 at 2:30 am

  #1996908

  Below is how I process various airline companies' classes:
  
  
DECLARE @xmlData XML;
SET @xmlData = '<?xml version="1.0"?>
<Air_classes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ROOT>
<Carrier>* </Carrier>
<Dep>*</Dep>
<Arr>*</Arr>
<ClassCode>A</ClassCode>
<ServLevel>F</ServLevel>
<RestLevel>*</RestLevel>
<PrivateFare>0</PrivateFare>
<Description>First Class Discounted</Description>
<Application></Application>
<ReqRoute>*</ReqRoute>
<ShowLevel>Z</ShowLevel>
</ROOT>
<ROOT>
<Carrier>* </Carrier>
<Dep>*</Dep>
<Arr>*</Arr>
<ClassCode>B</ClassCode>
<ServLevel>E</ServLevel>
<RestLevel>*</RestLevel>
<PrivateFare>0</PrivateFare>
<Description>Economy Restricted</Description>
<Application></Application>
<ReqRoute>*</ReqRoute>
<ShowLevel>A</ShowLevel>
</ROOT>
</Air_classes>';

  INSERT INTO [Air_Classes]
    ([Carrier]
    ,[Dep]
    ,[Arr]
    ,[ClassCode]
    ,[ServLevel]
    ,[RestLevel]
    ,[PrivateFare]
    ,[Description]
    ,[Application]
    ,[ReqRoute]
    ,[ShowLevel]
    ,[MatchToAvClasses])

  SELECT 
ref.value('Carrier[1]', 'NVARCHAR(10)') AS Carrier,
ref.value('Dep[1]', 'NVARCHAR (10)') AS Dep ,
ref.value('Arr[1]', 'NVARCHAR (10)') AS Arr ,
ref.value('ClassCode[1]', 'NVARCHAR (10)') AS ClassCode,
ref.value('ServLevel[1]', 'NVARCHAR (10)') AS ServLevel,
ref.value('RestLevel[1]', 'NVARCHAR (10)') AS RestLevel,
ref.value('PrivateFare[1]', 'BIT') AS PrivateFare,
ref.value('Description[1]', 'NVARCHAR (100)') AS [Description],
ref.value('Application[1]', 'NVARCHAR (100)') AS [Application],
ref.value('ReqRoute[1]', 'NVARCHAR (100)') AS ReqRoute,
ref.value('ShowLevel[1]', 'NVARCHAR (100)') AS ShowLevel,
ref.value('MatchToAvClasses[1]', 'NVARCHAR (100)') AS MatchToAvClasses
FROM @xmlData.nodes('/Air_classes/ROOT') 
xmlData( ref );

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  July 10, 2018 at 3:44 am

  #1996916

  BOR15K - Tuesday, July 10, 2018 2:30 AM

  Below is how I process various airline companies' classes:
  
  
  DECLARE @xmlData XML;
  SET @xmlData = '<?xml version="1.0"?>
  <Air_classes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ROOT>
  <Carrier>* </Carrier>
  <Dep>*</Dep>
  <Arr>*</Arr>
  <ClassCode>A</ClassCode>
  <ServLevel>F</ServLevel>
  <RestLevel>*</RestLevel>
  <PrivateFare>0</PrivateFare>
  <Description>First Class Discounted</Description>
  <Application></Application>
  <ReqRoute>*</ReqRoute>
  <ShowLevel>Z</ShowLevel>
  </ROOT>
  <ROOT>
  <Carrier>* </Carrier>
  <Dep>*</Dep>
  <Arr>*</Arr>
  <ClassCode>B</ClassCode>
  <ServLevel>E</ServLevel>
  <RestLevel>*</RestLevel>
  <PrivateFare>0</PrivateFare>
  <Description>Economy Restricted</Description>
  <Application></Application>
  <ReqRoute>*</ReqRoute>
  <ShowLevel>A</ShowLevel>
  </ROOT>
  </Air_classes>';

  INSERT INTO [Air_Classes]
      ([Carrier]
      ,[Dep]
      ,[Arr]
      ,[ClassCode]
      ,[ServLevel]
      ,[RestLevel]
      ,[PrivateFare]
      ,[Description]
      ,[Application]
      ,[ReqRoute]
      ,[ShowLevel]
      ,[MatchToAvClasses])

  SELECT 
  ref.value('Carrier[1]', 'NVARCHAR(10)') AS Carrier,
  ref.value('Dep[1]', 'NVARCHAR (10)') AS Dep ,
  ref.value('Arr[1]', 'NVARCHAR (10)') AS Arr ,
  ref.value('ClassCode[1]', 'NVARCHAR (10)') AS ClassCode,
  ref.value('ServLevel[1]', 'NVARCHAR (10)') AS ServLevel,
  ref.value('RestLevel[1]', 'NVARCHAR (10)') AS RestLevel,
  ref.value('PrivateFare[1]', 'BIT') AS PrivateFare,
  ref.value('Description[1]', 'NVARCHAR (100)') AS [Description],
  ref.value('Application[1]', 'NVARCHAR (100)') AS [Application],
  ref.value('ReqRoute[1]', 'NVARCHAR (100)') AS ReqRoute,
  ref.value('ShowLevel[1]', 'NVARCHAR (100)') AS ShowLevel,
  ref.value('MatchToAvClasses[1]', 'NVARCHAR (100)') AS MatchToAvClasses
  FROM @xmlData.nodes('/Air_classes/ROOT') 
  xmlData( ref );

  Suggest you change your query and add the text() function as I mentioned in my previous post, here is an example
  😎

  SELECT
ref.value('(Carrier/text())[1]', 'NVARCHAR(10)') AS Carrier,
ref.value('(Dep/text())[1]', 'NVARCHAR (10)') AS Dep ,
ref.value('(Arr/text())[1]', 'NVARCHAR (10)') AS Arr ,
ref.value('(ClassCode/text())[1]', 'NVARCHAR (10)') AS ClassCode,
ref.value('(ServLevel/text())[1]', 'NVARCHAR (10)') AS ServLevel,
ref.value('(RestLevel/text())[1]', 'NVARCHAR (10)') AS RestLevel,
ref.value('(PrivateFare/text())[1]', 'BIT') AS PrivateFare,
ref.value('(Description/text())[1]', 'NVARCHAR (100)') AS [Description],
ref.value('(Application/text())[1]', 'NVARCHAR (100)') AS [Application],
ref.value('(ReqRoute/text())[1]', 'NVARCHAR (100)') AS ReqRoute,
ref.value('(ShowLevel/text())[1]', 'NVARCHAR (100)') AS ShowLevel,
ref.value('(MatchToAvClasses/text())[1]', 'NVARCHAR (100)') AS MatchToAvClasses
FROM @xmlData.nodes('/Air_classes/ROOT')
xmlData( ref );


  The difference is obvious in the execution plans (simplified here to a single element value)
  Without the text() function
  SELECT ref.value('Carrier[1]', 'NVARCHAR(10)') AS Carrier --ref.value('Dep[1]', 'NVARCHAR (10)') AS Dep , --ref.value('Arr[1]', 'NVARCHAR (10)') AS Arr , --ref.value('ClassCode[1]', 'NVARCHAR (10)') AS ClassCode, --ref.value('ServLevel[1]', 'NVARCHAR (10)') AS ServLevel, --ref.value('RestLevel[1]', 'NVARCHAR (10)') AS RestLevel, --ref.value('PrivateFare[1]', 'BIT') AS PrivateFare, --ref.value('Description[1]', 'NVARCHAR (100)') AS [Description], --ref.value('Application[1]', 'NVARCHAR (100)') AS [Application], --ref.value('ReqRoute[1]', 'NVARCHAR (100)') AS ReqRoute, --ref.value('ShowLevel[1]', 'NVARCHAR (100)') AS ShowLevel, --ref.value('MatchToAvClasses[1]', 'NVARCHAR (100)') AS MatchToAvClasses FROM @xmlData.nodes('/Air_classes/ROOT') xmlData( ref );
|--Compute Scalar(DEFINE:([Expr1018]=[Expr1017]))
   |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id]))
    |--Filter(WHERE:(STARTUP EXPR([@xmlData] IS NOT NULL)))
    |  |--Table-valued function
    |--Stream Aggregate(DEFINE:([Expr1017]=MIN(CASE WHEN [@xmlData] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1014])>=(128) THEN CONVERT_IMPLICIT(nvarchar(10),[Expr1015],0) ELSE CONVERT_IMPLICIT(nvarchar(10),[Expr1014],0) END END)))
      |--UDX((XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1008], XML Reader with XPath filter.[id], XML Reader.[id]))
        |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1020]))
         |--Top(TOP EXPRESSION:((1)))
         |  |--Compute Scalar(DEFINE:([Expr1008]=0x58))
         |   |--Filter(WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))))
         |     |--Compute Scalar(DEFINE:([Expr1020]=getdescendantlimit(XML Reader with XPath filter.[id])))
         |       |--Table-valued function
         |--Table-valued function

  With the text() function
  SELECT ref.value('(Carrier/text())[1]', 'NVARCHAR(10)') AS Carrier --ref.value('(Dep/text())[1]', 'NVARCHAR (10)') AS Dep , --ref.value('(Arr/text())[1]', 'NVARCHAR (10)') AS Arr , --ref.value('(ClassCode/text())[1]', 'NVARCHAR (10)') AS ClassCode, --ref.value('(ServLevel/text())[1]', 'NVARCHAR (10)') AS ServLevel, --ref.value('(RestLevel/text())[1]', 'NVARCHAR (10)') AS RestLevel, --ref.value('(PrivateFare/text())[1]', 'BIT') AS PrivateFare, --ref.value('(Description/text())[1]', 'NVARCHAR (100)') AS [Description], --ref.value('(Application/text())[1]', 'NVARCHAR (100)') AS [Application], --ref.value('(ReqRoute/text())[1]', 'NVARCHAR (100)') AS ReqRoute, --ref.value('(ShowLevel/text())[1]', 'NVARCHAR (100)') AS ShowLevel, --ref.value('(MatchToAvClasses/text())[1]', 'NVARCHAR (100)') AS MatchToAvClasses FROM @xmlData.nodes('/Air_classes/ROOT') xmlData( ref );
|--Compute Scalar(DEFINE:([Expr1010]=[Expr1009]))
   |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id]))
    |--Filter(WHERE:(STARTUP EXPR([@xmlData] IS NOT NULL)))
    |  |--Table-valued function
    |--Stream Aggregate(DEFINE:([Expr1009]=MIN(CASE WHEN [@xmlData] IS NULL THEN NULL ELSE CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128) THEN CONVERT_IMPLICIT(nvarchar(10),XML Reader with XPath filter.[lvalue],0) ELSE CONVERT_IMPLICIT(nvarchar(10),XML Reader with XPath filter.[value],0) END END)))
      |--Top(TOP EXPRESSION:((1)))
        |--Compute Scalar(DEFINE:([Expr1008]=0x58))
         |--Filter(WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(2))))
           |--Table-valued function

  Without the text() function, the server has to reconstruct the XML for the output.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  July 10, 2018 at 8:27 am

  #1996971

  That's amazing. Didn't realize the text() had that impact. Any idea why?

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  July 10, 2018 at 9:06 am

  #1996986

  Steve Jones - SSC Editor - Tuesday, July 10, 2018 8:27 AM

  That's amazing. Didn't realize the text() had that impact. Any idea why?

  In simple terms, without the text() function, SQL Server has to reconstruct an XML for the output, even if it contains no nodes, only the value. If the text() function is applied, then the reconstruct is avoided. If you look at the execution plan, you can see the reconstruct (UDX) and an extra XML reader with a nested loop feeding into the XML construct.
  😎

  I've written a piece on this way back, will try to dig it up.