How do I Pivot on an XML column's attributes in T-SQL
Have a look at this dynamic pivot and more recently this one - you basically need to be able to SELECT DISTINCT FieldName to use this technique to build your query dynamically.
Here's the full answer for your particular problem (note that there is a column order weakness when generating the list from the distinct attributes in knowing what order the columns should appear):
DECLARE @template AS varchar(MAX)
SET @template = 'SELECT
FormEntryId
,{@col_list}
,DateCreated
FROM FormEntry'
DECLARE @col_template AS varchar(MAX)
SET @col_template = 'FormXML.value(''/Root[1]/Form[1]/FormData[@FieldName="{FieldName}"][1]/@FieldValue'',''varchar(max)'') AS {FieldName}'
DECLARE @col_list AS varchar(MAX)
;WITH FieldNames AS (
SELECT DISTINCT FieldName
FROM FormEntry
CROSS APPLY (
SELECT X.FieldName.value('@FieldName', 'varchar(255)')
FROM FormXML.nodes('/Root[1]/Form[1]/FormData') AS X(FieldName)
) AS Y (FieldName)
)
SELECT @col_list = COALESCE(@col_list + ',', '') + REPLACE(@col_template, '{FieldName}', FieldName)
FROM FieldNames
DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(@template, '{@col_list}', @col_list)
EXEC (@sql)
How do I PIVOT on an XML column?
You essentially need to assign a row number to each "row" of XML (or figure out some other way of distinguishing each "row"). One way you could go about this is by extracting the default key from each "row" (by using a window function and partitioning):
SELECT *
FROM
(
SELECT Name = C2.X.value('@name', 'varchar(max)'),
Val = C2.X.value('@value', 'varchar(max)'),
DefaultKey = MAX(CASE WHEN C2.X.value('@name', 'varchar(max)') = 'DefaultKey' THEN C2.X.value('@value', 'varchar(max)') END) OVER(PARTITION BY C1.X)
FROM myTable
CROSS APPLY myXMLColumn.nodes('table/rows/row/columns') AS C1(X)
CROSS APPLY C1.X.nodes('column') AS C2(X)
) AS T
PIVOT (MAX(Val) FOR Name IN ([DESC], [ec_amount], [ec_exrate], [ec_total], [ItemNo], [UOM])) AS P;
If DefaultKey weren't unique, you could use a row number or rank window function instead to each a similar result:
SELECT *
FROM
(
SELECT Name = C2.X.value('@name', 'varchar(max)'),
Val = C2.X.value('@value', 'varchar(max)'),
RowInXML = DENSE_RANK() OVER (ORDER BY C1.X)
FROM myTable
CROSS APPLY myXMLColumn.nodes('table/rows/row/columns') AS C1(X)
CROSS APPLY C1.X.nodes('column') AS C2(X)
) AS T
PIVOT (MAX(Val) FOR Name IN ([DefaultKey], [DESC], [ec_amount], [ec_exrate], [ec_total], [ItemNo], [UOM])) AS P;
Pivot XML into SQL Columns & values
You can better do this with XPath/XQuery than with OPENXML. Check out documentation on XML.nodes() and XML.value(). Check out some XPath guide online, this is a good one.
DECLARE @i XML=
'<index>
<doc id="0"><field name="MFG"><val>ACME</val></field><field name="InternalCode"><val /></field><field name="partnumber"><val>012345-00</val></field><field name="partdescription"><val>PIN</val></field></doc>
<doc id="1"><field name="MFG"><val /></field><field name="InternalCode"><val>ABCDE</val></field><field name="partnumber"><val>919-555-7Z</val></field><field name="partdescription"><val>WASHER</val></field></doc>
<doc id="2"><field name="MFG"><val>YOUR COMPANY</val></field><field name="InternalCode"><val /></field><field name="partnumber"><val>131415</val></field><field name="partdescription"><val>BOLT</val></field></doc>
</index>';
SELECT
rowid=n.v.value('@id','VARCHAR(40)'),
MFG=n.v.value('(field[@name="MFG"]/val)[1]','VARCHAR(40)'),
InternalCode=n.v.value('(field[@name="InternalCode"]/val)[1]','VARCHAR(40)'),
partnumber=n.v.value('(field[@name="partnumber"]/val)[1]','VARCHAR(40)'),
partdescription=n.v.value('(field[@name="partdescription"]/val)[1]','VARCHAR(40)')
FROM
@i.nodes('/index/doc') AS n(v);
Result:
+-------+--------------+--------------+------------+-----------------+
| rowid | MFG | InternalCode | partnumber | partdescription |
+-------+--------------+--------------+------------+-----------------+
| 0 | ACME | | 012345-00 | PIN |
| 1 | | ABCDE | 919-555-7Z | WASHER |
| 2 | YOUR COMPANY | | 131415 | BOLT |
+-------+--------------+--------------+------------+-----------------+
SQL pivot dynamic columns out of an nvarchar column containing xml
-- Sample data
declare @T table
(
ID int,
Title nvarchar(10),
FieldsXml nvarchar(max)
)
insert into @T values
(1, 'A', '<Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>'),
(2, 'B', '<Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>'),
(3, 'C', '<Fields><Field Name="Z">z3</Field></Fields>')
-- Create temp table
select T.ID,
T.Title,
TN.X.value('@Name', 'nvarchar(128)') as FieldName,
TN.X.value('.', 'nvarchar(max)') as FieldValue
into #tmp
from @T as T
cross apply (select cast(FieldsXml as XML)) as TX(X)
cross apply TX.X.nodes('/Fields/Field') as TN(X)
declare @ColList nvarchar(max)
declare @Sql nvarchar(max)
-- Build column list
select @ColList = stuff((select '], ['+FieldName
from #tmp
group by FieldName
for xml path('')), 1, 2, '')+']'
-- Build query
set @Sql = 'select *
from (select ID,
Title,
FieldName,
FieldValue
from #tmp
) as T
pivot (min(FieldValue) for FieldName in (' + @ColList + ')) as P'
exec (@Sql)
drop table #tmp
Pivot complex XML using Xquery
It is not so clear how to distinguish between languages:
- [productDetailUrl DE]
- [productDetailUrl EN]
Other than that, please try the following solution. It will get you started.
SQL
DECLARE @XML AS XML =
N'<?xml version="1.0"?>
<data xsi:schemaLocation="http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex catalog.xsd http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt dt.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex"
xmlns:xml="http://www.w3.org/XML/1998/namespace"
xmlns:dt="http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt"
major="6" minor="1" family="enfinity" branch="enterprise"
build="2.6.6-R-1.1.59.2-20210714.2">
<item sku="987654">
<sku>987654</sku>
<category-links>
<category-link name="abc" domain="WhiteStuff-DE-WebCategories"
default="0" hotdeal="0"/>
<category-link name="def" domain="WhiteStuff-DE-WebCategories"
default="1" hotdeal="0"/>
<category-link name="ghi" domain="WhiteStuff-DE-WebCategories"
default="0" hotdeal="0"/>
</category-links>
<images>
<primary-view image-view="FF"/>
<image-ref image-view="FD" image-type="w150"
image-base-name="FD.jpg" domain="WhiteStuff"/>
<image-ref image-view="FF" image-type="ORI" image-base-name="FF.jpg"
domain="WhiteStuff"/>
</images>
<variations>
<variation-attributes>
<variation-attribute name="size">
<presentation-option>default</presentation-option>
<custom-attributes>
<custom-attribute name="displayName" dt:dt="string"
xml:lang="en-US">Size</custom-attribute>
<custom-attribute name="productDetailUrl"
xml:lang="de-DE" dt:dt="string">123.co.uk</custom-attribute>
</custom-attributes>
</variation-attribute>
<variation-attribute name="colour">
<presentation-option>colorCode</presentation-option>
<presentation-product-attribute-name>rgbColour</presentation-product-attribute-name>
<custom-attributes>
<custom-attribute name="displayName" dt:dt="string"
xml:lang="en-US">Colour</custom-attribute>
<custom-attribute name="productDetailUrl"
xml:lang="de-DE" dt:dt="string">456.co.uk</custom-attribute>
</custom-attributes>
</variation-attribute>
</variation-attributes>
</variations>
</item>
</data>';
;WITH XMLNAMESPACES
(
DEFAULT 'http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex',
'http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt' as dt
)
SELECT c.value('@sku', 'nvarchar(max)') as [sku]
, n.value('@name','VARCHAR(20)') AS [category-link]
, c.value('(images/image-ref[@image-view="FD"]/@image-base-name)[1]','VARCHAR(20)') AS [FD image]
, c.value('(images/image-ref[@image-view="FF"]/@image-base-name)[1]','VARCHAR(20)') AS [FF image]
, c.value('(variations/variation-attributes/variation-attribute/custom-attributes/custom-attribute[@xml:lang="de-DE"]/text())[1]','VARCHAR(20)') AS [productDetailUrl DE]
, c.value('(variations/variation-attributes/variation-attribute[@name="colour"]/custom-attributes/custom-attribute[@xml:lang="de-DE"]/text())[1]','VARCHAR(20)') AS [productDetailUrl EN]
FROM @XML.nodes('/data/item') as t(c)
CROSS APPLY t.c.nodes('category-links/category-link') AS t2(n);
Output
+--------+---------------+----------+----------+---------------------+---------------------+
| sku | category-link | FD image | FF image | productDetailUrl DE | productDetailUrl EN |
+--------+---------------+----------+----------+---------------------+---------------------+
| 987654 | abc | FD.jpg | FF.jpg | 123.co.uk | 456.co.uk |
| 987654 | def | FD.jpg | FF.jpg | 123.co.uk | 456.co.uk |
| 987654 | ghi | FD.jpg | FF.jpg | 123.co.uk | 456.co.uk |
+--------+---------------+----------+----------+---------------------+---------------------+
Pivot XML using XQuery and filter on attribute
You're nearly there.
You need to use nodes() to shred the xml into the rows you want to work with - here, you want a resultset row for each section element, so shred with
nodes('/data/section')
Once you've done that, you just need to make your xpath [1] syntactically correct (and relative to the section nodes you will be 'in'):
data.value('(item[@id = "A"]/number)[1]', 'int') as A,
data.value('(item[@id = "B"]/number)[1]', 'int') as B,
data.value('(item[@id = "C"]/number)[1]', 'int') as C
And voila:
A B C
----------- ----------- -----------
987 654 321
123 456 789
SQL Server: Specify Column Name as Attribute and Node Value FOR XML
This is what you have to do ...
SELECT
'PURCHASE_DETAIL_ID' AS [Field/@FieldName],
'GOLI' + RTRIM(CONVERT(VARCHAR(10), ol.ID)) AS [Field],
'',
'ORDER_ID' AS [Field/@FieldName],
o.ID AS [Field],
'',
'PRODUCT_ID' AS [Field/@FieldName],
P.ID AS [Field]
FROM ...
WHERE ...
FOR XML PATH('Record'), ROOT('Records')
Extract all attribute values from XML column in SQL Server
I had to repair your XML, as the provided sample is not well-formed. So you might need to adapt this.
DECLARE @xml XML=
N'<ns2:pay xmlns:ns2="http://someurl.com/">
<ns2:e k="BID">
<ns2:l v="2"/>
</ns2:e>
<ns2:e k="PMD">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="GPTA5">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA4">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA3">
<ns2:s v="572"/>
</ns2:e>
<ns2:e k="GPTA1">
<ns2:s v="Sweet & Sour Sauce"/>
</ns2:e>
<ns2:e k="PFID">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="EAN">
<ns2:s v="010000"/>
</ns2:e>
<ns2:e k="PT">
<ns2:s v="1"/>
</ns2:e>
<ns2:e k="TXID1">
<ns2:l v="0"/>
</ns2:e>
<ns2:e k="PMN">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="DID">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="GPTA6">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA7">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="PLU">
<ns2:l v="10000"/>
</ns2:e>
<ns2:e k="GPTA8">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="DYT">
<ns2:s v="SWEET & SOUR SAUCE"/>
</ns2:e>
</ns2:pay>';
--This query will return all attributes with their names (a classic key-value-list)
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT e.value('@k','nvarchar(max)') AS AttributeName
,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
FROM @xml.nodes(N'/ns2:pay/ns2:e') A(e);
--This query allows you to pick the value of one given key
DECLARE @FindThis NVARCHAR(100)='BID';
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k=sql:variable("@FindThis")]/ns2:l/@v)[1]','int'); --use the proper type, if all values will be fine with this
--This query will return a table of your values (as long as you knwo all keys in advance)
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k="BID"]/ns2:l/@v)[1]','int') AS BID
,@xml.value(N'(/ns2:pay/ns2:e[@k="PMD"]/ns2:l/@v)[1]','nvarchar(max)') AS PMD
,@xml.value(N'(/ns2:pay/ns2:e[@k="GPTA4"]/ns2:l/@v)[1]','nvarchar(max)') AS GPTA4
--add all keys in the same way...
UPDATE
Here's an example to read this from a table using PIVOT to get it in tabular form:
Hint: I use NVARCHAR(1000) to simulate your need for a cast on-the-fly:
DECLARE @mockupTable TABLE(ID INT,YourData NVARCHAR(1000));
INSERT INTO @mockupTable VALUES
(1
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
<ns2:e k="BID">
<ns2:l v="2"/>
</ns2:e>
<ns2:e k="PMD">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="GPTA5">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA4">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA3">
<ns2:s v="572"/>
</ns2:e>
<!--shortened for brevity-->
</ns2:pay>')
,(2
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
<ns2:e k="BID">
<ns2:l v="20"/>
</ns2:e>
<ns2:e k="PMD">
<ns2:l v="10"/>
</ns2:e>
<ns2:e k="GPTA5">
<ns2:s v="bla"/>
</ns2:e>
<ns2:e k="GPTA4">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA3">
<ns2:s v="572"/>
</ns2:e>
<!--shortened for brevity-->
</ns2:pay>');
--The query will create a key-value list with the row's ID as grouping factor
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT p.*
FROM
(
SELECT ID
,e.value('@k','nvarchar(max)') AS AttributeName
,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
FROM @mockupTable t
--the cast happens here
CROSS APPLY(SELECT CAST(t.YourData AS XML)) A(TheXml)
--the call to .nodes() happens here to return a derived table
CROSS APPLY TheXml.nodes(N'/ns2:pay/ns2:e') B(e)
) tbl
PIVOT(MAX(AttributeValue)
FOR AttributeName
IN(BID,PMD,GPTA3,GPTA4,GPTA5) --add your columns here, order does not matter
) p
Syntax issue SQL Server. Combining Pivot, XML parse and JOIN
It is quite possible that I am missing something with your question, but it seems like you can just expand your existing query the following way. This still uses the CTE and the PIVOT, but the PIVOT query is placed in a subquery which allows you to join to table2:
; with cte as --imports a library of common table expressions
(
select TOP 1000 cast('<info ' + REPLACE(REPLACE(REPLACE(REPLACE(OtherInformation,' ', ''),',', '" '),'=','="'),'.','') + '" />' as XML) info
, ROW_NUMBER() over (order by TableID)) id
FROM yourtable
)
select d.userid, t2.col1, t2.col2
from
(
SELECT DISTINCT UserID
from
(
select T.N.value('local-name(.)', 'varchar(max)') as Name,
T.N.value('.', 'varchar(max)') as Value, id
from cte
cross apply info.nodes('//@*') as T(N)
) v
pivot
(
max(value)
for Name in ([UserID])
) p
) d
inner join table2 t2
on d.userid = t2.userid;
See SQL Fiddle with Demo
Pivot Data for XML Export in T-SQL
doesn't really form a valid xml since you're not specifying a root in your sample output but you can do something like this.
SELECT CAST('<' + element + ' attribute="' + attribute + '">' + data + '</' + element + '>' AS XML)
FROM Table1
FOR XML PATH('')
Related Topics
Postgres Won't Accept Table Alias Before Column Name
How to Call a User Defined Function to Use with Select, Group By, Order By
Ms SQL Server Last Inserted Id
What's the Asymptotic Complexity of Groupby Operation
How to Update a Blob in SQL Server Using Tsql
Bigquery - Joining on Multiple Conditions Using Subqueries and or Statements
Limit Ignored in Query with Group_Concat
Can Linq to SQL Query an Xml Field Db-Serverside
Create View Must Be the Only Statement in the Batch
Exists/Not Exists: 'Select 1' VS 'Select Field'
Update a Single Row with T-Sql
The Object Name Contains More Than the Maximum Number of Prefixes. the Maximum Is 3
Select Columns with Particular Column Names in Postgresql
SQL Server:Select Rows Who's Sum Matches a Value
Concat the Second Column Value If the First Column Value Is Same