Insert into table using XQuery and XML document

Question

Post reply

Insert into table using XQuery and XML document

qbrt

SSCrazy

Points: 2737
More actions
May 23, 2011 at 2:53 pm

#240159

I have the following xml document.
DECLARE @doc XML
SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>
<root name="TABLE_NAME_1">
<row action="U" timestamp="2011-05-18 17:14:21.000">
<column name="CRCUSN">86831</column>
<column name="CRSYER">111</column>
<column name="CRSFPD">1</column>
<column name="CRTSLD">3004.81</column>
<column name="CRTCRS">39.71</column>
<column name="CRFGAWD">148.21</column>
<column name="CRFGAVL">.01</column>
<column name="CRCRW#"></column>
<column name="CRFGVL">2001</column>
<column name="CRFGPC">5.01</column>
<column name="CREXPFL"></column>
</row>
<row>...</row>
.
.
</root>'
I have a sql table where I wish to insert the column values for each row. The sql table is defined like this:
CREATE TABLE [dbo].[TABLE_NAME_1](
[CRCUSN] [decimal](6, 0) NOT NULL,
[CRSYER] [decimal](3, 0) NOT NULL,
[CRSFPD] [decimal](2, 0) NOT NULL,
[CRTSLD] [decimal](9, 2) NOT NULL,
[CRTCRS] [decimal](9, 2) NOT NULL,
[CRFGAWD] [decimal](9, 2) NOT NULL,
[CRFGAVL] [decimal](9, 2) NOT NULL,
[CRCRW#] [varchar](15) NOT NULL,
[CRFGVL] [decimal](7, 0) NOT NULL,
[CRFGPC] [decimal](4, 2) NOT NULL,
[CREXPFL] [varchar](1) NOT NULL
)
When I issue the following query:
selectrow_number() over(order by t.c.value('../row[1]', 'nvarchar(10)')) as row_num,
t.c.value('../row[1]', 'nvarchar(20)') as row_id,
t.c.value('@name', 'nvarchar(100)') as column_name,
t.c.value('.', 'nvarchar(100)') as column_value
from@doc.nodes('/tpsfile/row') t(c)
The column_value = 8683111113004.8139.71148.21.0120015.01
Which is just a concatination of all the values for all the columns.
I am unable to figure out the proper XQuery to be able to insert the column values. The XML document contains multiple rows of data most of the time.
Maybe changing the xml document definition would help. Any suggestions greatly appreciated. Thank you.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 1

Check out this article: Loading XML Data into SQL Server (SQL Spackle)[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

qbrt SSCrazy Points: 2737 More actions · Answer 2

Thanks for the link. I did not come across this article, though I have been searching the Internet.

I'll read through this and get back on this topic.

Eddie Wuerch SSChampion Points: 12536 More actions · Answer 3

The link only gets you so far, as you have to pivot the results because your elements are key-value pairs instead of named elements.

I saw nothing in the sample xml that uniquely identified a row, so I used the timestamp of each row node to separate them. Not sure of that meets your requirements, but this will turn your xml into something that can be inserted into a table:

DECLARE @doc XML

SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>

<root name="TABLE_NAME_1">

<row action="U" timestamp="2011-05-18 17:14:21.000">

<column name="CRCUSN">86831</column>

<column name="CRSYER">111</column>

<column name="CRSFPD">1</column>

<column name="CRTSLD">3004.81</column>

<column name="CRTCRS">39.71</column>

<column name="CRFGAWD">148.21</column>

<column name="CRFGAVL">.01</column>

<column name="CRCRW#"></column>

<column name="CRFGVL">2001</column>

<column name="CRFGPC">5.01</column>

<column name="CREXPFL"></column>

</row>

<row action="U" timestamp="2011-05-18 17:15:21.000">

<column name="CRCUSN">10000</column>

<column name="CRSYER">555</column>

<column name="CRSFPD">1</column>

<column name="CRTSLD">100.0</column>

<column name="CRTCRS">55.55</column>

<column name="CRFGAWD">222.22</column>

<column name="CRFGAVL">.05</column>

<column name="CRCRW#">foo</column>

<column name="CRFGVL">999</column>

<column name="CRFGPC">12.22</column>

<column name="CREXPFL">x</column>

</row>

</root>';

WITH ColVals AS(

SELECT t.c.value('../@timestamp', 'char(23)') AS row_id,

t.c.value('../@action', 'char(1)') AS RowAction,

t.c.value('@name', 'nvarchar(100)') AS column_name,

t.c.value('(./text())[1]', 'nvarchar(100)') AS column_value

FROM @doc.nodes('/root/row/column') t(c)

)

SELECT row_num, RowAction, CRCUSN, CRSYER, CRSFPD, CRTSLD, CRTCRS, CRFGAWD, CRFGAVL, [CRCRW#], CRFGVL, CRFGPC, CREXPFL

FROM (SELECT DENSE_RANK() OVER (ORDER BY row_id) AS row_num, row_id, RowAction, column_name, column_value

FROM ColVals

) a

PIVOT(max(column_value) FOR column_name IN (CRCUSN, CRSYER, CRSFPD, CRTSLD, CRTCRS, CRFGAWD, CRFGAVL, [CRCRW#], CRFGVL, CRFGPC, CREXPFL)

) pvt

-Eddie

Eddie Wuerch
MCM: SQL

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Borrowing Eddie's excellent sample data...here is an alternative method without the pivot

DECLARE @doc XML

SET @doc = N'<?xml version="1.0" encoding="UTF-16" ?>

<root name="TABLE_NAME_1">

<row action="U" timestamp="2011-05-18 17:14:21.000">

<column name="CRCUSN">86831</column>

<column name="CRSYER">111</column>

<column name="CRSFPD">1</column>

<column name="CRTSLD">3004.81</column>

<column name="CRTCRS">39.71</column>

<column name="CRFGAWD">148.21</column>

<column name="CRFGAVL">.01</column>

<column name="CRCRW#"></column>

<column name="CRFGVL">2001</column>

<column name="CRFGPC">5.01</column>

<column name="CREXPFL"></column>

</row>

<row action="U" timestamp="2011-05-18 17:15:21.000">

<column name="CRCUSN">10000</column>

<column name="CRSYER">555</column>

<column name="CRSFPD">1</column>

<column name="CRTSLD">100.0</column>

<column name="CRTCRS">55.55</column>

<column name="CRFGAWD">222.22</column>

<column name="CRFGAVL">.05</column>

<column name="CRCRW#">foo</column>

<column name="CRFGVL">999</column>

<column name="CRFGPC">12.22</column>

<column name="CREXPFL">x</column>

</row>

</root>';

SELECT

nd.value('@action','char(1)') as [action]

, nd.value('@timestamp','datetime') as [timestamp]

, nd.value('(column[@name="CRCUSN"]/text())[1]','int') as [CRCUSN]

, nd.value('(column[@name="CRSYER"]/text())[1]','int') as [CRSYER]

, nd.value('(column[@name="CRSFPD"]/text())[1]','int') as [CRSFPD]

, nd.value('(column[@name="CRTSLD"]/text())[1]','decimal(12,2)') as [CRTSLD]

, nd.value('(column[@name="CRTCRS"]/text())[1]','decimal(12,2)') as [CRTCRS]

, nd.value('(column[@name="CRFGAWD"]/text())[1]','decimal(12,2)') as [CRFGAWD]

, nd.value('(column[@name="CRFGAVL"]/text())[1]','decimal(12,2)') as [CRFGAVL]

, nd.value('(column[@name="CRCRW#"]/text())[1]','nvarchar(10)') as [CRCRW#]

, nd.value('(column[@name="CRFGVL"]/text())[1]','int') as [CRFGVL]

, nd.value('(column[@name="CRFGPC"]/text())[1]','decimal(12,2)') as [CRFGPC]

, nd.value('(column[@name="CREXPFL"]/text())[1]','varchar(10)') as [CREXPFL]

FROM @doc.nodes('//row') x(nd)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

qbrt SSCrazy Points: 2737 More actions · Answer 5

Thank you! Both, the pivot and the XQuery solutions work.

After some deliberation I've ended up using the XQuery solution.

Main reason being that I am not a fan of the SQL pivot syntax. It always takes me a long time to get the pivots working. I was looking at this from a maintenance point of view, and I will have an easier time maintaining the XQuery.

I did not test if one performs better than the other.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

sandor.pakh (5/24/2011)
Thank you! Both, the pivot and the XQuery solutions work.
After some deliberation I've ended up using the XQuery solution.
Main reason being that I am not a fan of the SQL pivot syntax. It always takes me a long time to get the pivots working. I was looking at this from a maintenance point of view, and I will have an easier time maintaining the XQuery.
I did not test if one performs better than the other.

You're welcome - hopefully others will find uses for both methods and choose whichever suits them best as well.

I like your point about the PIVOT syntax being harder to maintain - I don't use it often and always have to look it up before I do, it's just not intuitive for me :w00t:.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);