CopyPaste.Net: xml

Showing posts with label xml. Show all posts

The XML source does not validate the data in the XML file against the XSD

Important to know:

The SSIS "XML source" element check validation of the input XML file,

but – check only the structure, not the data!

That mean not valid data will pass this validation check!

Is it good or not?

Depend on the case.

But don't forget it…

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/xml-source

Return the data from XML as a table

declare @ColumnXML xml =

'<XmlValues>

</XmlValues>

-- get all XML records

SELECT T.c.query('.') AS result

FROM @ColumnXML.nodes('/XmlValues/XmlValue') T(c)

-- return the data from the xml as a table

SELECT T.c.value('@SomeId','nvarchar(100)') as SomeId,

T.c.value('.', 'varchar(50)') as SomeValue

FROM @ColumnXML.nodes('/XmlValues/XmlValue') T(c)

-- also you can do filters:

where T.c.value('@SomeId','nvarchar(100)') = 2

FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')

An encoding issue with “FOR XML PATH”:

DECLARE @str NVARCHAR(MAX)= N'h&s_dor, Zoe&lee88AB'

select stuff(( select ',' + t.Alias from split ( @str, ',') t

FOR XML PATH(''))

,1,1,'') EncStr

Solution:

FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')

select stuff((( select ',' + t.Alias from split ( @str, ',') t

FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)'))

,1,1,'') EncStr

wrong XML parent

Problem:

XML - node is unfer the wrong parent

Cause and solution:

Check that the node is one of the items of the 'Order By', and check the order. The XML query should be sorted by the key nodes.

XML tag ID 4 that was originally declared as is being redeclared as

Error message:

XML tag ID 4 that was originally declared as Aaaaa is being redeclared as Bbbbb.

Cause and solution:

Each group of data should be declared in other tag.

In this case, there were 2 groups with tag 4: [Aaaaa!4!CollectionID] and [Bbbbb!4!CollectionID].

The solution was to create new tag to the new group: [Bbbbb!13!CollectionID].

Of course that I had to add new UNION select in order to select the data of Bbbbb and to assign it to a parent.

XML field value is NULL

T-SQL:

DECLARE @EventXML XML

SET @EventXML=convert(xml,N'

<Event>

<ExternalID>4947</ExternalID>

<ExternalEventId>abc</ExternalEventId>

</Event>')

DECLARE @hDoc int

exec sp_XML_PrepareDocument @hDoc output,@EventXML

select ExternalID, ExternalEventID

from

OPENXML(@hDoc,'//Event',2)

with

( ExternalID nvarchar(100),

ExternalEventID nvarchar(100)

) XML

exec sp_XML_RemoveDocument @hDoc

Results:

ExternalID = 4947, ExternalEventId = NULL

Problem:

ExternalEventId should be 'abc' and not NULL.

Solution:

XML is case sensitive.

ExternalEventId was set to the XML with 'd', and in the OPENXML is 'D'.

Replace one of them to be like the other.

select ExternalID, ExternalEventID

from

OPENXML(@hDoc,'//Event',2)

with

( ExternalID nvarchar(100),

ExternalEventId nvarchar(100)

) XML

Parent tag ID 1 is not among the open tags

Error message:

Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set

Cause and solution:

Wrong Parent Tag:

Check the parent field, make sure that the parent is not NULL (instead of the root), and that the Parent ID is already created and/or it's among the open tags.

Comment out the FOR XML EXPLICIT part of the query to get the results and understand the ordering.

Maybe you need to add/remove/change the order of the tag and/or the parent fields in the ORDER BY part of the query.

For XML Path

Select StamTableId,

Cast( ( Select StamTableStr, StamTableText

From StamTable T1

Where T1.StamTableId=T.StamTableId

For XML Path('Stam')

) As XML

) SQL

From StamTable T

http://msdn.microsoft.com/en-us/library/ms190922.aspx

http://vstoolsforum.com/blogs/sqlserver/archive/2008/08/26/for-xml-path.aspx

http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/

Import XML into SQL Server

The XML file (C:\SQL\some_xml.xml):

For this Type of XML:

note: C = C driver on the server.

SQL:

-- table to insert the data

CREATE TABLE #XML_Info (

ColumnA varchar(100),

ColumnB varchar(100),

ColumnInt numeric(5,2)

)

DECLARE @XMLData TABLE (XMLData XML)

INSERT INTO @XMLData

SELECT *

FROM OPENROWSET(BULK N'C:\SQL\some_xml.xml', SINGLE_BLOB) rs

select * from @XMLData

INSERT INTO #XML_Info (ColumnA, ColumnB, ColumnInt)

SELECT ColumnA = x.data.value('COLUMNA[1]','varchar(100)'),

ColumnB = x.data.value('COLUMNB[1]','varchar(100)'),

ColumnInt = x.data.value('COLUMNINT[1]','numeric(5,2)')

FROM @XMLData t

CROSS APPLY t.XMLData.nodes('/ROOTTAG/PARTAG') x(data)

SELECT * FROM #XML_Info

drop table #XML_Info

Result:

For this Type of XML:

<rows>
<row ColumnA="25759" ColumnB="171639" />
<row ColumnA="25762" ColumnB="171639" />
<row ColumnA="25763" ColumnB="171639" />
<row ColumnA="302665" ColumnB="152887" />

</rows>

DECLARE @MyTable TABLE
( ColumnA int, ColumnB int)
DECLARE @XMLData1 TABLE (XMLData XML)
DECLARE @XML1 XML, @hDoc1 int

INSERT INTO @XMLData1
SELECT * FROM OPENROWSET(BULK N'C:\SQL\some_xml.xml', SINGLE_BLOB) rs
SELECT @XML1 = XMLData from @XMLData1
--SELECT @XML1
exec sp_xml_preparedocument @hDoc1 output,@XML1

INSERT INTO @MyTable (ColumnA, ColumnB)
select *
from OPENXML(@hDoc1,'/rows/row') with (
ColumnA int '@ColumnA',
ColumnB int '@ColumnB'
)

exec sp_xml_removedocument @hDoc1

FOR XML EXPLICIT

SELECT 1 AS Tag,

NULL AS Parent,

e.EmployeeID AS [Employee!1!EmployeeID],

c.Name AS [Employee!1!Name],

NULL AS [Address!2!AddressLine1],

NULL AS [Address!2!PostalCode]

FROM HumanResources.Employee e(NOLOCK)

JOIN Person.Contact c (NOLOCK)

ON e.ContactID = c.ContactID

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

e.EmployeeID AS [Employee!1!EmployeeID],

NULL AS [Employee!1!Name],

AddressLine1 AS [Address!2!AddressLine1],

PostalCode AS [Address!2!PostalCode]

FROM HumanResources.Employee e(NOLOCK)

JOIN HumanResources.EmployeeAddress ea(NOLOCK)

ON e.EmployeeID = ea.EmployeeID

JOIN Person.Address a (NOLOCK)

ON ea.AddressID = a.AddressID

JOIN Person.StateProvince s (NOLOCK)

ON a.StateProvinceID = s.StateProvinceID

ORDER BY e.EmployeeID, Tag

FOR XML EXPLICIT, ROOT('Employees')

Result:

</Employee>

</Employee>

</Employees>

Labels